package com.jackrain.nea.utility;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Component;

import java.sql.Timestamp;
import java.util.*;
import java.util.function.Consumer;

/**
 * 标准的数据库操作
 */
@Mapper
@Component
public interface StandardlyMapper {
    static class SqlProvider {

        static final String[] READONLY_COLUMNS = new String[]{
                "OWNERID",
                "OWNERNAME",
                "OWNERENAME",
                "CREATIONDATE",
                "AD_CLIENT_ID",
                "AD_ORG_ID",
                "ID"};

        private static boolean isReadOnly(String column) {
            for (String readonlyColumn : READONLY_COLUMNS) {
                if (readonlyColumn.equals(column)) {
                    return true;
                }
            }
            return false;
        }

        private static boolean isNullOrEmpty(String str) {
            return str == null || str.length() == 0 || str.trim().length() == 0;
        }

        private static boolean isNullOrEmpty(JSONObject json) {
            return json == null || json.isEmpty();
        }

        private static void keyToUpper(JSONObject json) {
            String[] keys = new String[json.size()];
            json.keySet().toArray(keys);
            for (String key : keys) {
                if (!key.equals(key.toUpperCase())) {
                    json.put(key.toUpperCase(), json.get(key));
                    json.remove(key);
                }
            }
        }

        public String update(Map<String, Object> para) {
            final String tableName = (String) para.get("tableName");
            final JSONObject model = (JSONObject) para.get("model");
            if (isNullOrEmpty(tableName)) {
                throw new IllegalArgumentException("tableName 无效");
            }
            if (isNullOrEmpty(model) || model.size() == 1) {
                throw new IllegalArgumentException("model 无效");
            }
            keyToUpper(model);
            SQL sql = new SQL();
            sql.UPDATE(tableName);
            for (String key : model.keySet()) {
                if (!isReadOnly(key)) {
                    sql.SET(key + "= #{model." + key + "}");
                }
            }

            sql.WHERE("ID = #{model.ID}");
            return sql.toString();
        }

        public String inserts(Map<String, Object> para) {
            final String tableName = (String) para.get("tableName");
            final JSONObject[] models = (JSONObject[]) para.get("models");
            final Consumer<JSONObject> processor = (Consumer<JSONObject>) para.get("processor");
            if (isNullOrEmpty(tableName)) {
                throw new IllegalArgumentException("tableName 无效");
            }
            if (models == null || models.length == 0) {
                throw new IllegalArgumentException("model 无效");
            }


            //set values
            Set<String> columns = new HashSet<>();
            for (int i = 0; i < models.length; i++) {
                JSONObject model = models[i];
                if (model == null) {
                    models[i] = model = new JSONObject();
                }
                if (processor != null) {
                    processor.accept(model);
                }
                keyToUpper(model);
                //set columns
                columns.addAll(model.keySet());
            }
            //insert into tableName(column,column,column,...) values
            StringBuffer sql = new StringBuffer();
            sql.append("INSERT INTO ");
            sql.append(tableName);
            sql.append("(");
            sql.append(String.join(",", columns));
            sql.append(") VALUES");
            // (value,value,value,...),
            // (value,value,value,...)
            for (int i = 0; i < models.length; i++) {
                sql.append("(");
                for (String column : columns) {
                    if (models[i].containsKey(column)) {
                        sql.append(String.format("#{models[%d].%s}", i, column));
                    } else {
                        sql.append("default");
                    }
                    sql.append(",");
                }
                sql.setCharAt(sql.length() - 1, ')');
                sql.append(",");
            }
            sql.deleteCharAt(sql.length() - 1);
            return sql.toString();
        }

        public String insert(Map<String, Object> para) {
            final JSONObject model = (JSONObject) para.get("model");
            final String tableName = (String) para.get("tableName");
            if (isNullOrEmpty(tableName)) {
                throw new IllegalArgumentException("tableName 无效");
            }
            if (isNullOrEmpty(model)) {
                throw new IllegalArgumentException("model 无效");
            }
            SQL sql = new SQL();
            Set<String> keySet = model.keySet();
            sql.INSERT_INTO(tableName);
            for (int i = 0; i < model.size(); i++) {
                String key = (String) keySet.toArray()[i];
                sql.VALUES(key, "#{model." + key + "}");
            }
            return sql.toString();
        }

        public String delete(Map<String, Object> para) {
            final long[] ids = (long[]) para.get("ids");
            final String tableName = (String) para.get("tableName");
            if (isNullOrEmpty(tableName)) {
                throw new IllegalArgumentException("tableName 无效");
            }
            if (ids == null) {
                throw new IllegalArgumentException("ids 无效");
            }
            if (ids.length == 0) {
                return "";
            }
            if (ids.length == 1) {
                return "DELETE FROM " + tableName + " where ID = #{ids[0]}";
            }
            StringBuffer sql = new StringBuffer();
            sql.append("DELETE FROM ");
            sql.append(tableName);
            sql.append(" WHERE ");
            sql.append("ID in (");
            for (int i = 0; i < ids.length; i++) {
                sql.append(String.format("#{ids[%d]}", i));
                sql.append(",");
            }
            sql.setCharAt(sql.length() - 1, ')');
            return sql.toString();
        }

        public String updates(Map<String, Object> para) {

            String tableName = (String) para.get("tableName");
            final Collection<Object> models = (Collection<Object>) para.get("models");
            Consumer<JSONObject> processor = (Consumer<JSONObject>) para.get("processor");
            Boolean first = true;
            if (isNullOrEmpty(tableName)) {
                throw new IllegalArgumentException("tableName 无效");
            }
            if (models == null || models.size() == 0) {
                throw new IllegalArgumentException("model 无效");
            }
            JSONObject columnall = new JSONObject(true);
            for (Object obj : models) {
                JSONObject columnpart = (JSONObject) obj;
                if (columnpart == null) {
                    columnpart = new JSONObject(true);
                }
                if (processor != null) {
                    processor.accept(columnpart);
                }
                keyToUpper(columnpart);
                columnall.putAll(columnpart);
            }
            JSONObject clonekeyobj = (JSONObject) columnall.clone();
            Set<String> keys = columnall.keySet();
            for (String column : keys) {
                if (isReadOnly(column)) {
                    clonekeyobj.remove(column);
                }
            }
            Set<String> firstkeys = clonekeyobj.keySet();
            StringBuffer sql = new StringBuffer("");
            sql.append("UPDATE ").append(tableName).append(" a");
            sql.append(" right join (");
            int i = -1;
            for (Object obj : models) {
                i++;
                JSONObject model = (JSONObject) obj;
                if (first) {
                    sql.append(" SELECT #{models[0].ID} `ID`,");
                    for (String columnkey : firstkeys) {
                        if (model.get(columnkey) == null) {
                            model.put(columnkey, null);
                        }
                        sql.append(String.format("#{models[%d].%s} ", i, columnkey));
                        sql.append("`").append(columnkey).append("`").append(",");

                    }
                    sql.deleteCharAt(sql.length() - 1);
                    if (models.size() > 0) {
                        sql.append(" union all");
                    }
                    first = false;
                } else {
                    sql.append(" SELECT ");
                    sql.append(String.format("#{models[%d].%s} ", i, "ID")).append(",");
                    for (String key : firstkeys) {
                        if (model.get(key) == null) {
                            model.put(key, null);
                        }
                        sql.append(String.format("#{models[%d].%s} ", i, key));
                        sql.append(",");
                    }
                    sql.deleteCharAt(sql.length() - 1);
                    if (i < models.size() - 1) {
                        sql.append(" union all");
                    }
                }
            }
            sql.append(") b on a.ID=b.ID ");
            sql.append(" SET ");
            for (String value : firstkeys) {

                sql.append(" a.").append(value).append("=").append("IFNULL(b.").append(value).append(",").append("a.").append(value).append(")").append(",");
            }
            sql.deleteCharAt(sql.length() - 1);
            return sql.toString();
        }
    }

    /**
     * 根据主键id更新数据库
     *
     * @param tableName 表名
     * @param model     待更新实体
     * @return 返回受影响行数
     */
    @UpdateProvider(type = SqlProvider.class, method = "update")
    int updateById(@Param("tableName") String tableName, @Param("model") JSONObject model);

    /**
     * 根据一组主键id删除对应数据
     *
     * @param tableName 表名
     * @param ids       主键
     * @return 返回受影响行数
     */
    @DeleteProvider(type = SqlProvider.class, method = "delete")
    int deleteByIds(@Param("tableName") String tableName, @Param("ids") long[] ids);

    /**
     * 根据主键id删除对应数据
     *
     * @param tableName 表名
     * @param id        主键
     * @return 返回受影响行数
     */
    @Delete("DELETE FROM ${tableName} where ID = #{id}")
    int deleteById(@Param("tableName") String tableName, @Param("id") long id);

    /**
     * 根据主键id删除对应数据
     *
     * @param tableName 表名
     * @param id        主键
     * @return 返回受影响行数
     */
    @Select("select * FROM ${tableName} where ID = #{id}")
    JSONObject getById(@Param("tableName") String tableName, @Param("id") long id);

    /**
     * 根据主键id删除对应数据
     *
     * @param tableName 表名
     * @param id        主键
     * @return 返回受影响行数
     */
    @Select("select ${columns} FROM ${tableName} where ID = #{id}")
    JSONObject getColumnsById(@Param("tableName") String tableName, @Param("columns") String columns, @Param("id") long id);

    /**
     * 插入数据
     *
     * @param tableName 表名
     * @param model     待插入的实体
     * @return 返回受影响行数
     */
    @InsertProvider(type = SqlProvider.class, method = "insert")
    int insert(@Param("tableName") String tableName, @Param("model") JSONObject model);

    /**
     * 批量插入数据
     *
     * @param tableName 表名
     * @param models    待插入的实体集合
     * @param processor 实体加工程序, 可以为null
     * @return 存在返回 true, 反之返回 false
     */
    @InsertProvider(type = SqlProvider.class, method = "inserts")
    int inserts(@Param("tableName") String tableName, @Param("models") JSONObject[] models, @Param("processor") Consumer<JSONObject> processor);

    /**
     * 查询指定数据是否存在
     *
     * @param tableName 表名
     * @param column    列名
     * @param value     值
     * @return 存在返回 true, 反之返回 false
     */
    @Select("select count(1) FROM ${tableName} where ${column} = #{value} limit 1")
    boolean exists(@Param("tableName") String tableName, @Param("column") String column, @Param("value") Object value);

    /**
     * 查询指定主键id的数据是否存在
     *
     * @param tableName 表名
     * @param id        主键id
     * @return
     */
    @Select("select count(1) FROM ${tableName} where ID = #{id} limit 1")
    boolean existsById(@Param("tableName") String tableName, @Param("id") long id);


    /**
     * 判断指定条件的数据,在排除特定id之后,是否存在
     *
     * @param tableName 表名
     * @param id        需要排除数据的主键id
     * @param column    列名
     * @param value     值
     * @return 存在返回 true, 反之返回 false
     */
    @Select("select count(1) FROM ${tableName} where id <> #{id} and ${column} = #{value} limit 1")
    boolean existsWithoutId(@Param("tableName") String tableName, @Param("id") long id, @Param("column") String column, @Param("value") Object value);

    /**
     * 根据多个主键id批量更新数据库，不支持字段赋空值的修改
     *
     * @param tableName 表名
     * @param models    待更新实体
     * @param processor 实体加工程序, 可以为null
     * @return 返回受影响行数
     */
    @UpdateProvider(type = StandardlyMapper.SqlProvider.class, method = "updates")
    int updates(@Param("tableName") String tableName, @Param("models") Collection<Object> models, @Param("processor") Consumer<JSONObject> processor);
}

